package cn.edu.nju.software.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.sql.DataSource;

import cn.edu.nju.software.bean.show.CommentSearchListItem;
import cn.edu.nju.software.bean.storage.Comment;
import cn.edu.nju.software.dao.intf.CommentDao;
import cn.edu.nju.software.util.Constants;

public class CommentDaoImpl implements CommentDao {

	private DataSource ds = null;

	public CommentDaoImpl() {
		try {
			ds = JDBCUtil.getDataSource();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public List<CommentSearchListItem> searchComments(
			Map<Integer, Object> criteria) throws SQLException {
		List<CommentSearchListItem> list = new ArrayList<CommentSearchListItem>();
		StringBuilder sql = new StringBuilder(
				"SELECT c.id, c.user_id, c.book_id, c.content, "
						+ "c.post_time, c.sticky, u.act_name, b.title FROM comment c, bmuser u, "
						+ "book b WHERE c.user_id = u.id AND c.book_id = b.id");
		if (criteria.containsKey(Constants.SEARCH_USER)) {
			sql.append(" AND u.act_name = ?");
		}
		if (criteria.containsKey(Constants.SEARCH_TITLE)) {
			sql.append(" AND b.title = ?");
		}
		if (criteria.containsKey(Constants.SEARCH_DATE)) {
			sql.append("AND c.post_time >= ? AND c.post_time <= ?");
		}
		sql.append(" ORDER BY c.book_id, c.post_time DESC");
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql.toString());
			int count = 1;
			if (criteria.containsKey(Constants.SEARCH_USER)) {
				pstmt.setString(count,
						(String) criteria.get(Constants.SEARCH_USER));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_TITLE)) {
				pstmt.setString(count,
						(String) criteria.get(Constants.SEARCH_TITLE));
				++count;
			}
			if (criteria.containsKey(Constants.SEARCH_DATE)) {
				@SuppressWarnings("unchecked")
				List<Date> dates = (List<Date>) criteria
						.get(Constants.SEARCH_DATE);
				pstmt.setTimestamp(count, new Timestamp(dates.get(0).getTime()));
				++count;
				pstmt.setTimestamp(count, new Timestamp(dates.get(1).getTime()));
				++count;
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new CommentSearchListItem(new Comment(rs.getInt(1), rs
						.getInt(2), rs.getInt(3), rs.getString(4), rs
						.getTimestamp(5), rs.getTimestamp(6)), rs.getString(7),
						rs.getString(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public int postComment(Comment comment) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(
					"INSERT INTO comment (user_id, book_id, "
							+ "content, post_time) VALUES (?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			pstmt.setInt(1, comment.getUserID());
			pstmt.setInt(2, comment.getBookID());
			pstmt.setString(3, comment.getContent());
			pstmt.setTimestamp(4, comment.getPostTime());
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				result = rs.getInt(1);
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public void updateComment(Comment comment) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE comment SET sticky = ? WHERE id = ?");
			pstmt.setTimestamp(1, comment.getStickyTime());
			pstmt.setInt(2, comment.getId());
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
	}

	@Override
	public List<Comment> getCommentByUserId(int userID) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Comment> list = new ArrayList<Comment>();
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT id, book_id, content, "
					+ "post_time, sticky FROM comment WHERE user_id = ? "
					+ "ORDER BY book_id, post_time DESC");
			pstmt.setInt(1, userID);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new Comment(rs.getInt(1), userID, rs.getInt(2), rs
						.getString(3), rs.getTimestamp(4), rs.getTimestamp(5)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<CommentSearchListItem> getCommentByBookID(int bookID)
			throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<CommentSearchListItem> list = new ArrayList<CommentSearchListItem>();
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT c.id, c.user_id, c.content, "
							+ "c.post_time, c.sticky, u.act_name, b.title FROM comment c, bmuser u, "
							+ "book b WHERE c.user_id = u.id AND c.book_id = b.id "
							+ "AND c.book_id = ? ORDER BY c.post_time DESC");
			pstmt.setInt(1, bookID);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new CommentSearchListItem(new Comment(rs.getInt(1), rs
						.getInt(2), bookID, rs.getString(3),
						rs.getTimestamp(4), rs.getTimestamp(5)), rs
						.getString(6), rs.getString(7)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<CommentSearchListItem> getAllComments() throws SQLException {
		// TODO Auto-generated method stub
		List<CommentSearchListItem> list = new ArrayList<CommentSearchListItem>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT c.id, c.user_id, c.book_id, c.content, "
							+ "c.post_time, c.sticky, u.act_name, b.title FROM comment c, bmuser u, "
							+ "book b WHERE c.user_id = u.id AND c.book_id = b.id");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new CommentSearchListItem(new Comment(rs.getInt(1), rs
						.getInt(2), rs.getInt(3), rs.getString(4), rs
						.getTimestamp(5), rs.getTimestamp(6)), rs.getString(7),
						rs.getString(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

}
